package com.pengcheng.hnweb.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.pengcheng.hnweb.interceptor.Saveinterceptor;
import com.pengcheng.hnweb.job.TestJob;
import com.pengcheng.hnweb.service.DbService;
import com.pengcheng.hnweb.util.*;
import jakarta.servlet.http.HttpServletRequest;
import org.springframework.amqp.rabbit.core.RabbitTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.validation.ObjectError;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.file.Paths;
import java.util.*;

@RestController
public class ServiceController {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private RabbitTemplate rabbitTemplate;
    @Autowired
    private Common common;
    @Autowired
    private ExcelUtil excelUtil;
    @Autowired
    private JsonUtil jsonUtil;
    @Autowired
    private RabbitUtil rabbitUtil;
    @Autowired
    private FileUtil fileUtil;
    @Autowired
    private TestJob testJob;

    @Autowired
    private DbService dbService;
    @Autowired
    private Saveinterceptor saveinterceptor;

    private static final String UPLOAD_DIR = "public/uploads/";

    @RequestMapping("/service/list_device")
    private Map<String, Object> list_device()
    {
        String sql="select * from hnww_device where deleted=0";
        List<Map<String,Object>> data=jdbcTemplate.queryForList(sql);
        Map<String,Object> res=new HashMap<>();
        res.put("code",1);
        res.put("msg","success");
        res.put("data",data);
        return res;
    }
    @RequestMapping("/service/update_name")
    private Map<String,Object> update_name(@RequestParam("device_id") Integer device_id,@RequestParam("name") String name)
    {
        String sql="update hnww_device set name=? where id=?";
        Object[] ps={name,device_id};
        jdbcTemplate.update(sql,ps);
        Map<String,Object> res=new HashMap<>();
        res.put("code",1);
        res.put("msg","success");
        return res;
    }
    @RequestMapping("/service/run_sql")
    private Map<String,Object> run_sql(@RequestParam("sql") String sql)
    {
        List<Map<String,Object>> data=jdbcTemplate.queryForList(sql);
        Map<String,Object> res=new HashMap<>();
        res.put("code",1);
        res.put("msg","success");
        res.put("data",data);
        return res;
    }
    @RequestMapping("/service/execute_sql")
    private Map<String,Object> execute_sql(@RequestParam("sql") String sql)
    {
        int data=jdbcTemplate.update(sql);
        Map<String,Object> res=new HashMap<>();
        res.put("code",1);
        res.put("msg","success");
        res.put("data",data);
        return res;
    }
    @RequestMapping("/service/do_login")
    private Map<String,Object> do_login(HttpServletRequest request, @RequestParam("username") String username, @RequestParam("password") String password)
    {
        String sql="select * from hnww_user where username=? and password=? and deleted=0";
        Object[] ps={username,common.md5(password)};
        List<Map<String,Object>> list_user=jdbcTemplate.queryForList(sql,ps);
        Map<String,Object> res=new HashMap<>();
        if(list_user.size()>0)
        {
            res.put("code",1);
            res.put("msg","success");
            res.put("data",list_user.get(0));
            request.getSession().setAttribute("user_id",list_user.get(0).get("id").toString());
        }
        else {
            res.put("code",0);
            res.put("msg","用户名或密码错误"+username+password);
        }
        return res;
    }
    @RequestMapping("/service/change_password")
    private Map<String,Object> change_password(HttpServletRequest request, @RequestParam("user_id") int user_id, @RequestParam("old_password") String old_password, @RequestParam("new_password") String new_password)
    {
        Map<String,Object> res=new HashMap<>();
        String sql="select * from hnww_user where id="+user_id+" and password='"+common.md5(old_password)+"'";
        List<Map<String,Object>> rows_user=jdbcTemplate.queryForList(sql);
        if(rows_user==null||rows_user.size()==0)
        {
            res.put("code",0);
            res.put("msg","原密码错误");
            return res;
        }
        sql="update hnww_user set password='"+common.md5(new_password)+"' where id="+user_id;
        jdbcTemplate.update(sql);
        res.put("code",0);
        res.put("msg","修改密码成功");
        return res;
    }
    @RequestMapping("/service/flist")
    private Map<String,Object> flist(HttpServletRequest request, @RequestParam("table") String table, @RequestParam("condition") String condition, @RequestParam("order") String order, @RequestParam("limit") String limit, @RequestParam("detail") String detail)
    {
        Map<String,Object> res=new HashMap<>();
        ObjectMapper mapper=new ObjectMapper();
        try {
            // 将JSON字符串转换为Map对象
            Map<String, String[]> map = mapper.readValue(condition, new TypeReference<Map<String, String[]>>() {});
            List<Map<String,Object>> columns=dbService.columns(table);
            List<Map<String,Object>> rows=dbService.query(columns,table,map,order,limit);
            if(!common.is_empty(detail)&&detail.equals("1")&&rows.size()>0)
            {
                if(table.equals("resource"))
                {
                    rows=dbService.get_resource_detail(rows);
                }
                else if(table.equals("user"))
                {
                    rows=dbService.get_user_detail(rows);
                }
                else if(table.equals("limit"))
                {
                    rows=dbService.get_limit_detail(rows);
                }
                else if(table.equals("fee"))
                {
                    rows=dbService.get_fee_detail(rows);
                }
                else if(table.equals("device"))
                {
                    rows=dbService.get_device_detail(rows);
                }
                else if(table.equals("device_wq"))
                {
                    rows=dbService.get_device_wq_detail(rows);
                }
                else if(table.equals("device_level"))
                {
                    rows=dbService.get_device_level_detail(rows);
                }
                else if(table.equals("device_rain"))
                {
                    rows=dbService.get_device_rain_detail(rows);
                }
                else if(table.equals("device_video"))
                {
                    rows=dbService.get_device_video_detail(rows);
                }
                else if(table.equals("site"))
                {
                    rows=dbService.get_site_detail(rows);
                }
                else if(table.equals("field"))
                {
                    rows=dbService.get_field_detail(rows);
                }
                else if(table.equals("plan"))
                {
                    rows=dbService.get_plan_detail(rows);
                }
                else if(table.equals("feeitem"))
                {
                    rows=dbService.get_feeitem_detail(rows);
                }
                else if(table.equals("river"))
                {
                    rows=dbService.get_river_detail(rows);
                }
                else if(table.equals("article"))
                {
                    rows=dbService.get_article_detail(rows);
                }
                else if(table.equals("category"))
                {
                    rows=dbService.get_category_detail(rows);
                }
                else if(table.equals("alarm"))
                {
                    rows=dbService.get_alarm_detail(rows);
                }
                else if(table.equals("menu"))
                {
                    rows=dbService.get_menu_detail(rows);
                }
                else if(table.equals("roledetail"))
                {
                    rows=dbService.get_roledetail_detail(rows);
                }
                else if(table.equals("advice"))
                {
                    rows=dbService.get_advice_detail(rows);
                }
                else if(table.equals("adviceprocess"))
                {
                    rows=dbService.get_adviceprocess_detail(rows);
                }
                else if(table.equals("task"))
                {
                    rows=dbService.get_task_detail(rows);
                }
                else if(table.equals("taskprocess"))
                {
                    rows=dbService.get_taskprocess_detail(rows);
                }
                else if(table.equals("repair"))
                {
                    rows=dbService.get_repair_detail(rows);
                }
                else if(table.equals("video"))
                {
                    rows=dbService.get_video_detail(rows);
                }
                else if(table.equals("permit"))
                {
                    rows=dbService.get_permit_detail(rows);
                }
                else if(table.equals("site_dayflow"))
                {
                    rows=dbService.get_site_dayflow_detail(rows);
                }
                else if(table.equals("site_monthflow"))
                {
                    rows=dbService.get_site_monthflow_detail(rows);
                }
                else if(table.equals("dayflow"))
                {
                    rows=dbService.get_dayflow_detail(rows);
                }
                else if(table.equals("monthflow"))
                {
                    rows=dbService.get_monthflow_detail(rows);
                }
                else if(table.equals("quarterflow"))
                {
                    rows=dbService.get_quarterflow_detail(rows);
                }
                else if(table.equals("yearflow"))
                {
                    rows=dbService.get_yearflow_detail(rows);
                }
                else if(table.equals("site_dayflow"))
                {
                    rows=dbService.get_site_dayflow_detail(rows);
                }
                else if(table.equals("site_monthflow"))
                {
                    rows=dbService.get_site_monthflow_detail(rows);
                }
                else if(table.equals("site_quarterflow"))
                {
                    rows=dbService.get_site_quarterflow_detail(rows);
                }
                else if(table.equals("site_yearflow"))
                {
                    rows=dbService.get_site_yearflow_detail(rows);
                }
            }
            res.put("code",1);
            res.put("msg","success");
            res.put("data",rows);
        } catch (IOException e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }

    @RequestMapping("/service/fexport")
    private Map<String,Object> fexport(HttpServletRequest request, @RequestParam("table") String table, @RequestParam("condition") String condition, @RequestParam("order") String order, @RequestParam("limit") String limit, @RequestParam("detail") String detail)
    {
        Map<String,Object> res=new HashMap<>();
        res.put("code",1);
        res.put("msg","success");
        ObjectMapper mapper=new ObjectMapper();
        String uuid=(UUID.randomUUID().toString().replace("-",""));
        String filename=System.getProperty("user.dir")+"/hnweb/static/public/excel/"+uuid+".xlsx";
        String path="/public/excel/"+uuid+".xlsx";
        ExcelWriter builder=excelUtil.get_builder(filename,table);
        if(builder==null)
        {
            res.put("code",0);
            res.put("msg","创建写入器失败");
            return res;
        }
        try {
        WriteSheet sheet= EasyExcel.writerSheet(table).build();
            // 将JSON字符串转换为Map对象
            int pagesize=100;
            Map<String, String[]> map = mapper.readValue(condition, new TypeReference<Map<String, String[]>>() {});
            List<Map<String,Object>> columns=dbService.columns(table);
            int fcount=dbService.count(columns,table,map);
            int page=fcount%pagesize>0?(1+(int)(fcount/pagesize)):(int)(fcount/pagesize);
            List<String> list_time=new ArrayList<>();
            for(int i=0;i<page;i++)
            {
                limit=(i*pagesize)+","+pagesize;
                List<Map<String,Object>> rows=dbService.query(columns,table,map,order,limit);
                list_time.add(common.format_date(new Date()));
                if(!common.is_empty(detail)&&detail.equals("1")&&rows.size()>0)
                {
                    if(table.equals("resource"))
                    {
                        rows=dbService.get_resource_detail(rows);
                    }
                    else if(table.equals("user"))
                    {
                        rows=dbService.get_user_detail(rows);
                    }
                    else if(table.equals("limit"))
                    {
                        rows=dbService.get_limit_detail(rows);
                    }
                    else if(table.equals("fee"))
                    {
                        rows=dbService.get_fee_detail(rows);
                    }
                    else if(table.equals("device"))
                    {
                        rows=dbService.get_device_detail(rows);
                    }
                    else if(table.equals("device_wq"))
                    {
                        rows=dbService.get_device_wq_detail(rows);
                    }
                    else if(table.equals("device_level"))
                    {
                        rows=dbService.get_device_level_detail(rows);
                    }
                    else if(table.equals("device_rain"))
                    {
                        rows=dbService.get_device_rain_detail(rows);
                    }
                    else if(table.equals("device_video"))
                    {
                        rows=dbService.get_device_video_detail(rows);
                    }
                    else if(table.equals("site"))
                    {
                        rows=dbService.get_site_detail(rows);
                    }
                    else if(table.equals("field"))
                    {
                        rows=dbService.get_field_detail(rows);
                    }
                    else if(table.equals("plan"))
                    {
                        rows=dbService.get_plan_detail(rows);
                    }
                    else if(table.equals("feeitem"))
                    {
                        rows=dbService.get_feeitem_detail(rows);
                    }
                    else if(table.equals("river"))
                    {
                        rows=dbService.get_river_detail(rows);
                    }
                    else if(table.equals("article"))
                    {
                        rows=dbService.get_article_detail(rows);
                    }
                    else if(table.equals("category"))
                    {
                        rows=dbService.get_category_detail(rows);
                    }
                    else if(table.equals("alarm"))
                    {
                        rows=dbService.get_alarm_detail(rows);
                    }
                    else if(table.equals("menu"))
                    {
                        rows=dbService.get_menu_detail(rows);
                    }
                    else if(table.equals("roledetail"))
                    {
                        rows=dbService.get_roledetail_detail(rows);
                    }
                    else if(table.equals("advice"))
                    {
                        rows=dbService.get_advice_detail(rows);
                    }
                    else if(table.equals("adviceprocess"))
                    {
                        rows=dbService.get_adviceprocess_detail(rows);
                    }
                    else if(table.equals("task"))
                    {
                        rows=dbService.get_task_detail(rows);
                    }
                    else if(table.equals("taskprocess"))
                    {
                        rows=dbService.get_taskprocess_detail(rows);
                    }
                    else if(table.equals("repair"))
                    {
                        rows=dbService.get_repair_detail(rows);
                    }
                    else if(table.equals("video"))
                    {
                        rows=dbService.get_video_detail(rows);
                    }
                    else if(table.equals("dayflow"))
                    {
                        rows=dbService.get_dayflow_detail(rows);
                    }
                    else if(table.equals("monthflow"))
                    {
                        rows=dbService.get_monthflow_detail(rows);
                    }
                    else if(table.equals("site_dayflow"))
                    {
                        rows=dbService.get_site_dayflow_detail(rows);
                    }
                    else if(table.equals("site_monthflow"))
                    {
                        rows=dbService.get_site_monthflow_detail(rows);
                    }
                    else if(table.equals("site_quarterflow"))
                    {
                        rows=dbService.get_site_quarterflow_detail(rows);
                    }
                    else if(table.equals("site_yearflow"))
                    {
                        rows=dbService.get_site_yearflow_detail(rows);
                    }
                }
                list_time.add(common.format_date(new Date()));
                List<List<String>> data=excelUtil.get_data(table,rows);
                if(data==null)
                {
                    throw new RuntimeException("创建第"+(i+1)+"页数据失败");
                }
                builder.write(data,sheet);
                list_time.add(common.format_date(new Date()));
            }
            builder.finish();
            res.put("data",path);
            res.put("list_time",list_time);
        } catch (Exception e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        finally {
            if(builder!=null)
            {
                builder.finish();
            }
        }
        return res;
    }
    @RequestMapping("/service/fimport")
    private Map<String,Object> fimport(HttpServletRequest request, @RequestParam("table") String table)
    {
        Map<String,Object> res=new HashMap<>();
        res.put("code",1);
        res.put("msg","success");
        String uuid=(UUID.randomUUID().toString().replace("-",""));
        String filename=System.getProperty("user.dir")+"/hnweb/static/public/excel/"+uuid+".xlsx";
        String path="/public/excel/"+uuid+".xlsx";
        try {
            boolean result=excelUtil.make_template(filename,table);
            if(result)
            {
                res.put("data",path);
            }
            else
            {
                res.put("code",0);
                res.put("msg","生成模板失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }
    @RequestMapping("/service/fupdate")
    private Map<String,Object> fupdate(HttpServletRequest request, @RequestParam("table") String table, @RequestParam("row") String row)
    {
        Map<String,Object> res=new HashMap<>();
        ObjectMapper mapper=new ObjectMapper();
        try {
            // 将JSON字符串转换为Map对象
            Map<String, Object> map = mapper.readValue(row, new TypeReference<Map<String, Object>>() {});
            List<Map<String,Object>> columns=dbService.columns(table);
            int code=dbService.save(columns,table,map);
            if(code==-1)
            {
                res.put("code",0);
                res.put("msg","row转换map失败");
            }
            else {
                res.put("code",1);
                res.put("msg","success");
                res.put("data",code);
                res=saveinterceptor.after(table,map,res);
            }
        } catch (Exception e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
            fileUtil.add_msg("fupdate",common.format_exception(e));
        }
        return res;
    }
    @RequestMapping("/service/fcount")
    private Map<String,Object> fcount(HttpServletRequest request, @RequestParam("table") String table, @RequestParam("condition") String condition)
    {
        Map<String,Object> res=new HashMap<>();
        ObjectMapper mapper=new ObjectMapper();
        try {
            // 将JSON字符串转换为Map对象
            Map<String, String[]> map = mapper.readValue(condition, new TypeReference<Map<String, String[]>>() {});
            List<Map<String,Object>> columns=dbService.columns(table);
            int fcount=dbService.count(columns,table,map);
            res.put("code",1);
            res.put("msg","success");
            res.put("data",fcount);
        } catch (IOException e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }
    @RequestMapping("/service/fsum")
    private Map<String,Object> fsum(HttpServletRequest request, @RequestParam("table") String table, @RequestParam("condition") String condition, @RequestParam("field") String field)
    {
        Map<String,Object> res=new HashMap<>();
        ObjectMapper mapper=new ObjectMapper();
        try {
            // 将JSON字符串转换为Map对象
            Map<String, String[]> map = mapper.readValue(condition, new TypeReference<Map<String, String[]>>() {});
            List<Map<String,Object>> columns=dbService.columns(table);
            BigDecimal fsum=dbService.sum(columns,table,map,field);
            res.put("code",1);
            res.put("msg","success");
            res.put("data",fsum.doubleValue());
        } catch (IOException e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }
    @RequestMapping("/service/upload_image")
    private Map<String,Object> upload_image(HttpServletRequest request,@RequestParam("file") MultipartFile file)
    {
        Map<String,Object> res=new HashMap<>();
        ObjectMapper mapper=new ObjectMapper();
        if (file.isEmpty()) {
            res.put("code",0);
            res.put("msg","文件为空");
            return res;
        }
        try {
            String path = System.getProperty("user.dir")+"/hnweb/static/public/upload";
            File f = new File(path);
            if(!f.exists()){//不存在
                f.mkdirs();
            }
            String uuid = System.currentTimeMillis()+"";
            String oldName = file.getOriginalFilename();//1.jpg
            String suffix = oldName.substring(oldName.lastIndexOf("."));
            String newFile = uuid+suffix;
            File file2 = new File(path+"/"+newFile);
            file.transferTo(file2);
            res.put("location","/public/upload/"+newFile);
        } catch (IOException e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }
    @RequestMapping("/service/up_image")
    private Map<String,Object> up_image(HttpServletRequest request,@RequestParam("file") MultipartFile file)
    {
        Map<String,Object> res=new HashMap<>();
        ObjectMapper mapper=new ObjectMapper();
        if (file.isEmpty()) {
            res.put("code",0);
            res.put("msg","文件为空");
            return res;
        }
        try {
            String path = System.getProperty("user.dir")+"/hnweb/static/public/upload";
            File f = new File(path);
            if(!f.exists()){//不存在
                f.mkdirs();
            }
            String uuid = System.currentTimeMillis()+"";
            String oldName = file.getOriginalFilename();//1.jpg
            String suffix = oldName.substring(oldName.lastIndexOf("."));
            String newFile = uuid+suffix;
            File file2 = new File(path+"/"+newFile);
            file.transferTo(file2);
            Map<String,String> params=new HashMap<>();
            Map<String,String[]> map=request.getParameterMap();
            for(String key:map.keySet())
            {
                params.put(key,common.join(map.get(key)));
            }
            res.put("code",1);
            res.put("data","/public/upload/"+newFile);
            res.put("params",params);
        } catch (IOException e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }
    @RequestMapping("/service/up_video")
    private Map<String,Object> up_video(HttpServletRequest request,@RequestParam("file") MultipartFile file)
    {
        Map<String,Object> res=new HashMap<>();
        ObjectMapper mapper=new ObjectMapper();
        if (file.isEmpty()) {
            res.put("code",0);
            res.put("msg","文件为空");
            return res;
        }
        try {
            String path = System.getProperty("user.dir")+"/hnweb/static/public/upload";
            File f = new File(path);
            if(!f.exists()){//不存在
                f.mkdirs();
            }
            String uuid = System.currentTimeMillis()+"";
            String oldName = file.getOriginalFilename();//1.jpg
            String suffix = oldName.substring(oldName.lastIndexOf("."));
            String newFile = uuid+suffix;
            String imgFile = uuid+".jpg";
            File file2 = new File(path+"/"+newFile);
            file.transferTo(file2);
            common.get_cover(path+"/"+newFile,path+"/"+imgFile);
            Map<String,String> params=new HashMap<>();
            Map<String,String[]> map=request.getParameterMap();
            for(String key:map.keySet())
            {
                params.put(key,common.join(map.get(key)));
            }
            res.put("code",1);
            res.put("data","/public/upload/"+newFile);
            res.put("cover","/public/upload/"+imgFile);
            res.put("params",params);
        } catch (IOException e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }
    @RequestMapping("/service/up_file")
    private Map<String,Object> up_file(HttpServletRequest request,@RequestParam("file") MultipartFile file)
    {
        Map<String,Object> res=new HashMap<>();
        ObjectMapper mapper=new ObjectMapper();
        if (file.isEmpty()) {
            res.put("code",0);
            res.put("msg","文件为空");
            return res;
        }
        try {
            String path = System.getProperty("user.dir")+"/hnweb/static/public/upload";
            File f = new File(path);
            if(!f.exists()){//不存在
                f.mkdirs();
            }
            String uuid = System.currentTimeMillis()+"";
            String oldName = file.getOriginalFilename();//1.jpg
            String suffix = oldName.substring(oldName.lastIndexOf("."));
            if(suffix.toLowerCase().equals("jsp"))
            {
                res.put("code",0);
                res.put("msg","文件类型验证失败");
                return res;
            }
//            String newFile = uuid+suffix;
            String newFile = uuid+"_"+oldName;
            File file2 = new File(path+"/"+newFile);
            file.transferTo(file2);
            Map<String,String> params=new HashMap<>();
            Map<String,String[]> map=request.getParameterMap();
            for(String key:map.keySet())
            {
                params.put(key,common.join(map.get(key)));
            }
            res.put("code",1);
            res.put("data","/public/upload/"+newFile);
            res.put("params",params);
        } catch (IOException e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }
    @RequestMapping("/service/up_excel")
    private Map<String,Object> up_excel(HttpServletRequest request,@RequestParam("file") MultipartFile file)
    {
        Map<String,Object> res=new HashMap<>();
        ObjectMapper mapper=new ObjectMapper();
        if (file.isEmpty()) {
            res.put("code",0);
            res.put("msg","文件为空");
            return res;
        }
        try {
            String path = System.getProperty("user.dir")+"/fsmes/static/public/upload";
            File f = new File(path);
            if(!f.exists()){//不存在
                f.mkdirs();
            }
            String uuid = System.currentTimeMillis()+"";
            String oldName = file.getOriginalFilename();//1.jpg
            String suffix = oldName.substring(oldName.lastIndexOf("."));
            if(suffix.toLowerCase().equals("jsp"))
            {
                res.put("code",0);
                res.put("msg","文件类型验证失败");
                return res;
            }
//            String newFile = uuid+suffix;
            String newFile = uuid+"_"+oldName;
            File file2 = new File(path+"/"+newFile);
            file.transferTo(file2);
            Map<String,String> params=new HashMap<>();
            Map<String,String[]> map=request.getParameterMap();
            for(String key:map.keySet())
            {
                params.put(key,common.join(map.get(key)));
            }
            List<Map<Integer,String>> data=excelUtil.read(path+"/"+newFile);
            res.put("code",1);
            res.put("data",data);
            res.put("params",params);
        } catch (IOException e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }
    //
    @RequestMapping("/service/update_role")
    private Map<String,Object> update_role(HttpServletRequest request,@RequestParam("row_role") String str_row_role,@RequestParam("rows_menu") String str_rows_menu)
    {
        Map<String,Object> result=new HashMap<>();
        ObjectMapper mapper=new ObjectMapper();
        try {
            Map<String, Object> row_role = mapper.readValue(str_row_role, new TypeReference<Map<String, Object>>() {});
            List<Map<String, Object>> rows_menu = mapper.readValue(str_rows_menu, new TypeReference<List<Map<String, Object>>>() {});
            if(row_role==null)
            {
                String msg="角色信息转换map对象失败";
                result.put("code",0);
                result.put("msg",msg);
                return result;
            }
            if(rows_menu==null)
            {
                String msg="权限信息转换数组失败";
                result.put("code",0);
                result.put("msg",msg);
                return result;
            }
            int role_id=0;
            if(row_role.get("id")==null)
            {
                Map<String,Object> res=this.fupdate(request,"role",str_row_role);
                if(res.get("code")==null)
                {
                    String msg="添加角色失败";
                    result.put("code",0);
                    result.put("msg",msg);
                    return result;
                }
                if(!common.is_int(res.get("code")+""))
                {
                    String msg="添加角色返回code不是整数";
                    result.put("code",0);
                    result.put("msg",msg);
                    return result;
                }
                int code=Integer.parseInt(res.get("code")+"");
                if(code<=0)
                {
                    String msg=res.get("msg")+"";
                    result.put("code",0);
                    result.put("msg",msg);
                    return result;
                }
                if(res.get("data")==null||!common.is_int(res.get("data")+""))
                {
                    String msg="添加角色返回data为空或不是整数";
                    result.put("code",0);
                    result.put("msg",msg);
                    return result;
                }
                role_id=Integer.parseInt(res.get("data")+"");
            }
            else {
                if(!common.is_int(row_role.get("id")+""))
                {
                    String msg="角色数据中ID属性不是整数";
                    result.put("code",0);
                    result.put("msg",msg);
                    return result;
                }
                Map<String,Object> res=this.fupdate(request,"role",str_row_role);
                if(res.get("code")==null)
                {
                    String msg="添加角色失败";
                    result.put("code",0);
                    result.put("msg",msg);
                    return result;
                }
                if(!common.is_int(res.get("code")+""))
                {
                    String msg="添加角色返回code不是整数";
                    result.put("code",0);
                    result.put("msg",msg);
                    return result;
                }
                int code=Integer.parseInt(res.get("code")+"");
                if(code<=0)
                {
                    String msg=res.get("msg")+"";
                    result.put("code",0);
                    result.put("msg",msg);
                    return result;
                }
                role_id=Integer.parseInt(row_role.get("id")+"");
            }
            if(role_id<=0)
            {
                String msg="角色ID异常";
                result.put("code",0);
                result.put("msg",msg);
                return result;
            }
            //清空roledetail
            String sql="delete from hnww_roledetail where role_id="+role_id;
            jdbcTemplate.update(sql);
            //循环添加
            for(int i=0;i<rows_menu.size();i++)
            {
                Map<String,Object> row_menu=rows_menu.get(i);
                Map<String,Object> row_roledetail=new HashMap<>();
                row_roledetail.put("role_id",role_id);
                row_roledetail.put("menu_id",row_menu.get("id")+"");
                row_roledetail.put("power",row_menu.get("power")+"");
                row_roledetail.put("deleted",0);
                Map<String,Object> res=this.fupdate(request,"roledetail", JSONObject.toJSONString(row_roledetail));
                if("0".equals(res.get("code")+""))
                {
                    return res;
                }
            }
            result.put("code",1);
            result.put("msg","编辑成功");
            return result;
        }
        catch (Exception ex)
        {
            result.put("code",0);
            result.put("msg",common.format_exception(ex));
            return result;
        }
    }
    @RequestMapping("/service/synctime")
    private Map<String,Object> synctime(HttpServletRequest request,@RequestParam("id") String id)
    {
        Map<String,Object> result=new HashMap<>();
        String sql="select * from hnww_device where id="+id;
        List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
        if(rows_device!=null&&rows_device.size()>0)
        {
            Map<String,Object> row_device=rows_device.get(0);
            String msg= common.get_time_msg(new Date());
            String channel_id=row_device.get("channel_id").toString();
            Map<String,Object> res=new HashMap<>();
            res.put("channel_id",channel_id);
            res.put("msg",msg);
            rabbitTemplate.convertAndSend("hnww.respon",res);
            sql="select * from hnww_synctime where device_id="+row_device.get("id").toString();
            List<Map<String,Object>> rows_synctime=jdbcTemplate.queryForList(sql);
            if(rows_synctime==null||rows_synctime.size()==0)
            {
                sql="insert into hnww_synctime (device_id,fdate,deleted) values (?,?,0)";
                Object[] ps1={row_device.get("id").toString(),common.format_date(new Date())};
                jdbcTemplate.update(sql,ps1);
            }
            else {
                sql="update hnww_synctime set fdate=? where id="+rows_synctime.get(0).get("id").toString();
                Object[] ps2={common.format_date(new Date())};
                jdbcTemplate.update(sql,ps2);
            }
            result.put("code",1);
            result.put("msg","更新成功");
            result.put("data",msg);
        }
        else {
            result.put("code",0);
            result.put("msg","查询设备失败");
        }
        return result;
    }
    @RequestMapping("/service/update_interval")
    private Map<String,Object> update_interval(HttpServletRequest request,@RequestParam("id") int id,@RequestParam("interval") int interval)
    {
        Map<String,Object> result=new HashMap<>();
        try
        {
            //查询device
            String sql="";
            sql="select * from hnww_device where id=?";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql,new Object[]{id});
            if(list_device.size()==0)
            {
                throw new Exception("查询设备为空");
            }
            JSONObject row_device=JSONObject.parseObject(JSONObject.toJSONString(list_device.get(0)));
            int state=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.state");
            if(state<0)
            {
                throw new Exception("查询状态失败");
            }
            if(state==0)
            {
                result.put("code",0);
                result.put("msg","设备未上线");
                return result;
            }
            String channel_id=jsonUtil.get_string_by_path(row_device.toJSONString(),"$.channel_id");
            String msg=common.set_interval_msg(interval);
            rabbitUtil.respon(channel_id,msg);
            result.put("code",1);
            result.put("msg","设置成功，请重启");
        }
        catch (Exception ex)
        {
            result.put("code",0);
            result.put("msg",common.format_exception(ex));
        }
        return result;
    }
    @RequestMapping("/service/send_msg")
    private Map<String,Object> send_msg(HttpServletRequest request,@RequestParam("id") int id,@RequestParam("msg") String msg)
    {
        Map<String,Object> result=new HashMap<>();
        try
        {
            //查询device
            String sql="";
            sql="select * from hnww_device where id=?";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql,new Object[]{id});
            if(list_device.size()==0)
            {
                throw new Exception("查询设备为空");
            }
            JSONObject row_device=JSONObject.parseObject(JSONObject.toJSONString(list_device.get(0)));
            int state=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.state");
            if(state<0)
            {
                throw new Exception("查询状态失败");
            }
            if(state==0)
            {
                result.put("code",0);
                result.put("msg","设备未上线");
                return result;
            }
            String channel_id=jsonUtil.get_string_by_path(row_device.toJSONString(),"$.channel_id");
            rabbitUtil.respon(channel_id,msg);
            result.put("code",1);
            result.put("msg","发送成功");
        }
        catch (Exception ex)
        {
            result.put("code",0);
            result.put("msg",common.format_exception(ex));
        }
        return result;
    }
    @RequestMapping("/service/reboot")
    private Map<String,Object> reboot(HttpServletRequest request,@RequestParam("id") int id)
    {
        Map<String,Object> result=new HashMap<>();
        try
        {
            //查询device
            String sql="";
            sql="select * from hnww_device where id=?";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql,new Object[]{id});
            if(list_device.size()==0)
            {
                throw new Exception("查询设备为空");
            }
            JSONObject row_device=JSONObject.parseObject(JSONObject.toJSONString(list_device.get(0)));
            int state=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.state");
            if(state<0)
            {
                throw new Exception("查询状态失败");
            }
            if(state==0)
            {
                result.put("code",0);
                result.put("msg","设备未上线");
                return result;
            }
            String channel_id=jsonUtil.get_string_by_path(row_device.toJSONString(),"$.channel_id");
            rabbitUtil.restart(channel_id);
            result.put("code",1);
            result.put("msg","重启成功");
        }
        catch (Exception ex)
        {
            result.put("code",0);
            result.put("msg",common.format_exception(ex));
        }
        return result;
    }
    @RequestMapping("/service/clear_cache")
    private Map<String,Object> clear_cache(HttpServletRequest request,@RequestParam("id") int id)
    {
        Map<String,Object> result=new HashMap<>();
        try
        {
            //查询device
            String sql="";
            sql="select * from hnww_device where id=?";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql,new Object[]{id});
            if(list_device.size()==0)
            {
                throw new Exception("查询设备为空");
            }
            JSONObject row_device=JSONObject.parseObject(JSONObject.toJSONString(list_device.get(0)));
            int state=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.state");
            if(state<0)
            {
                throw new Exception("查询状态失败");
            }
            if(state==0)
            {
                result.put("code",0);
                result.put("msg","设备未上线");
                return result;
            }
            String channel_id=jsonUtil.get_string_by_path(row_device.toJSONString(),"$.channel_id");
            rabbitUtil.clear_cache(channel_id);
            result.put("code",1);
            result.put("msg","清除成功");
        }
        catch (Exception ex)
        {
            result.put("code",0);
            result.put("msg",common.format_exception(ex));
        }
        return result;
    }
    @RequestMapping("/service/set_hbeat")
    private Map<String,Object> set_hbeat(HttpServletRequest request,@RequestParam("id") int id,@RequestParam("hbeat") String hbeat)
    {
        Map<String,Object> result=new HashMap<>();
        try
        {
            //查询device
            String sql="";
            sql="select * from hnww_device where id=?";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql,new Object[]{id});
            if(list_device.size()==0)
            {
                throw new Exception("查询设备为空");
            }
            JSONObject row_device=JSONObject.parseObject(JSONObject.toJSONString(list_device.get(0)));
            int state=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.state");
            if(state<0)
            {
                throw new Exception("查询状态失败");
            }
            if(state==0)
            {
                result.put("code",0);
                result.put("msg","设备未上线");
                return result;
            }
            String channel_id=jsonUtil.get_string_by_path(row_device.toJSONString(),"$.channel_id");
            String msg=common.set_hbeat_msg(hbeat);
            rabbitUtil.respon(channel_id,msg);
            result.put("code",1);
            result.put("msg","设置成功，请重启");
        }
        catch (Exception ex)
        {
            result.put("code",0);
            result.put("msg",common.format_exception(ex));
        }
        return result;
    }
    @RequestMapping("/service/open_dsend")
    private Map<String,Object> open_dsend(HttpServletRequest request,@RequestParam("id") int id)
    {
        Map<String,Object> result=new HashMap<>();
        try
        {
            //查询device
            String sql="";
            sql="select * from hnww_device where id=?";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql,new Object[]{id});
            if(list_device.size()==0)
            {
                throw new Exception("查询设备为空");
            }
            JSONObject row_device=JSONObject.parseObject(JSONObject.toJSONString(list_device.get(0)));
            int state=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.state");
            if(state<0)
            {
                throw new Exception("查询状态失败");
            }
            if(state==0)
            {
                result.put("code",0);
                result.put("msg","设备未上线");
                return result;
            }
            String channel_id=jsonUtil.get_string_by_path(row_device.toJSONString(),"$.channel_id");
            String msg=common.set_dsend_msg();
            rabbitUtil.respon(channel_id,msg);
            msg=common.open_dsend_msg();
            rabbitUtil.respon(channel_id,msg);
            result.put("code",1);
            result.put("msg","设置成功，请重启");
        }
        catch (Exception ex)
        {
            result.put("code",0);
            result.put("msg",common.format_exception(ex));
        }
        return result;
    }
    @RequestMapping("/service/open_iccd")
    private Map<String,Object> open_iccd(HttpServletRequest request,@RequestParam("id") int id)
    {
        Map<String,Object> result=new HashMap<>();
        try
        {
            //查询device
            String sql="";
            sql="select * from hnww_device where id=?";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql,new Object[]{id});
            if(list_device.size()==0)
            {
                throw new Exception("查询设备为空");
            }
            JSONObject row_device=JSONObject.parseObject(JSONObject.toJSONString(list_device.get(0)));
            int state=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.state");
            if(state<0)
            {
                throw new Exception("查询状态失败");
            }
            if(state==0)
            {
                result.put("code",0);
                result.put("msg","设备未上线");
                return result;
            }
            String channel_id=jsonUtil.get_string_by_path(row_device.toJSONString(),"$.channel_id");
            rabbitUtil.open_iccd(channel_id);
            result.put("code",1);
            result.put("msg","设置成功，请重启");
        }
        catch (Exception ex)
        {
            result.put("code",0);
            result.put("msg",common.format_exception(ex));
        }
        return result;
    }

    @RequestMapping("/service/close_dsend")
    private Map<String,Object> close_dsend(HttpServletRequest request,@RequestParam("id") int id)
    {
        Map<String,Object> result=new HashMap<>();
        try
        {
            //查询device
            String sql="";
            sql="select * from hnww_device where id=?";
            List<Map<String,Object>> list_device=jdbcTemplate.queryForList(sql,new Object[]{id});
            if(list_device.size()==0)
            {
                throw new Exception("查询设备为空");
            }
            JSONObject row_device=JSONObject.parseObject(JSONObject.toJSONString(list_device.get(0)));
            int state=jsonUtil.get_int_by_path(row_device.toJSONString(),"$.state");
            if(state<0)
            {
                throw new Exception("查询状态失败");
            }
            if(state==0)
            {
                result.put("code",0);
                result.put("msg","设备未上线");
                return result;
            }
            String channel_id=jsonUtil.get_string_by_path(row_device.toJSONString(),"$.channel_id");
            String msg=common.close_dsend_msg();
            rabbitUtil.respon(channel_id,msg);
            result.put("code",1);
            result.put("msg","设置成功，请重启");
        }
        catch (Exception ex)
        {
            result.put("code",0);
            result.put("msg",common.format_exception(ex));
        }
        return result;
    }

    @RequestMapping("/service/get_power")
    private Map<String,Object> get_power(HttpServletRequest request,@RequestParam("user_id") String user_id,@RequestParam("menu_id") String menu_id)
    {
        Map<String,Object> result=new HashMap<>();
        Map<String,Object> row_power=new HashMap<>();
        String sql="select * from hnww_roledetail where menu_id="+menu_id+" and role_id in (select role_id from hnww_user where id="+user_id+")";
        List<Map<String,Object>> rows_roledetail=jdbcTemplate.queryForList(sql);
        if(rows_roledetail!=null&&rows_roledetail.size()>0)
        {
            Map<String,Object> row_roledetail=rows_roledetail.get(0);
            if(row_roledetail.get("power")!=null&&!row_roledetail.get("power").toString().equals(""))
            {
                String[] list_power=row_roledetail.get("power").toString().split(",");
                for(int i=0;i<list_power.length;i++)
                {
                    row_power.put("p"+list_power[i],1);
                }
            }
        }
        Map<String,Object> row_area=new HashMap<>();
        row_area.put("area0",0);
        row_area.put("area1",0);
        row_area.put("area2",0);
        row_area.put("area3",0);
        sql="select * from hnww_role where id in (select role_id from hnww_user where id="+user_id+")";
        List<Map<String,Object>> rows_role=jdbcTemplate.queryForList(sql);
        if(rows_role!=null&&rows_role.size()>0)
        {
            Map<String,Object> row_role=rows_role.get(0);
            row_area=row_role;
        }
        result.put("code",1);
        result.put("message","success");
        result.put("data",row_power);
        result.put("row_area",row_area);
        return result;
    }
    @RequestMapping("/service/get_site_info")
    private Map<String,Object> get_site_info(HttpServletRequest request)
    {
        Map<String,Object> result=new HashMap<>();
        String now=common.format_date(new Date());
        int year= common.get_year(new Date());
        //获取许可证总数量
        BigDecimal sum_flimit=new BigDecimal(0);
        String sql="select sum(flimit) as sum_flimit from hnww_permit where deleted=0 and sdate<=? and edate>=?";
        Object[] ps1=new Object[]{now,now};
        List<Map<String,Object>> rows_sum_flimit=jdbcTemplate.queryForList(sql,ps1);
        if(rows_sum_flimit.size()>0)
        {
            if(rows_sum_flimit.get(0).get("sum_flimit")!=null)
            {
                if(common.is_number(rows_sum_flimit.get(0).get("sum_flimit")+""))
                {
                    sum_flimit=new BigDecimal(rows_sum_flimit.get(0).get("sum_flimit")+"");
                }
            }
        }
        sql="select * from hnww_yearplan where year="+year;
        List<Map<String,Object>> rows_yearplan=jdbcTemplate.queryForList(sql);
        sql="select * from hnww_site_sum_monthflow where dyear="+year;
        List<Map<String,Object>> rows_sum_monthflow=jdbcTemplate.queryForList(sql);
        JSONArray rows_site_info=new JSONArray();
        BigDecimal sum_year_plan=new BigDecimal(0);
        BigDecimal sum_year_flow=new BigDecimal(0);
        for(int i=1;i<=12;i++)
        {
            JSONObject row_site_info=new JSONObject();
            //获取计划量
            BigDecimal sum_plan=new BigDecimal(0);
            if(rows_yearplan.size()>0)
            {
                if(common.is_number(rows_yearplan.get(0).get("sum_plan"+i)+""))
                {
                    sum_plan=new BigDecimal(rows_yearplan.get(0).get("sum_plan"+i)+"").setScale(2,RoundingMode.HALF_UP);
                }
            }
            sum_year_plan=sum_year_plan.add(sum_plan);
            //获取实际用水量
            BigDecimal sum_flow=new BigDecimal(0);
            for(int j=0;j<rows_sum_monthflow.size();j++)
            {
                if((i+"").equals(rows_sum_monthflow.get(j).get("dmonth")+"")&&common.is_number(rows_sum_monthflow.get(j).get("sum_flow")+""))
                {
                    sum_flow=new BigDecimal(rows_sum_monthflow.get(j).get("sum_flow")+"");
                }
            }
            sum_year_flow=sum_year_flow.add(sum_flow);
            row_site_info.put("year",year);
            row_site_info.put("month",i);
            row_site_info.put("sum_plan",sum_plan.setScale(2,RoundingMode.HALF_UP).doubleValue());
            row_site_info.put("sum_flow",sum_flow.setScale(2,RoundingMode.HALF_UP).doubleValue());
            rows_site_info.add(row_site_info);
        }
        result.put("code",1);
        result.put("message","success");
        result.put("sum_limit",sum_flimit.setScale(2, RoundingMode.HALF_UP).doubleValue());
        result.put("sum_year_plan",sum_year_plan.setScale(2, RoundingMode.HALF_UP).doubleValue());
        result.put("sum_year_flow",sum_year_flow.setScale(2, RoundingMode.HALF_UP).doubleValue());
        result.put("rows_site_info",rows_site_info);
        return result;
    }
    @RequestMapping("/service/get_fee_info")
    private Map<String,Object> get_fee_info(HttpServletRequest request)
    {
        //当年总费用，已交费用，未缴费用，超额用水总量，每月超额用水总量，某月超额明细
        Map<String,Object> result=new HashMap<>();
        String now=common.format_date(new Date());
        int year= common.get_year(new Date());
        BigDecimal sum_fee=new BigDecimal(0);
        BigDecimal sum_fee1=new BigDecimal(0);
        BigDecimal sum_fee0=new BigDecimal(0);
        BigDecimal sum_above_fee=new BigDecimal(0);
        BigDecimal sum_above_flow=new BigDecimal(0);
        JSONArray rows_above_info=new JSONArray();//包含year,month,sum_above_fee,sum_above_fee
        String sql="SELECT sum(fee) as sum_fee FROM `hnww_fee` where deleted=0 and year="+year;
        JSONArray rows_sum_fee=JSONArray.parseArray(JSONArray.toJSONString(jdbcTemplate.queryForList(sql)));
        if(rows_sum_fee!=null&&rows_sum_fee.size()>0)
        {
            sum_fee=new BigDecimal(jsonUtil.get_double_by_path(rows_sum_fee.getJSONObject(0).toJSONString(),"$.sum_fee"));
            if(sum_fee.doubleValue()<0)
            {
                sum_fee=new BigDecimal(0);
            }
        }
        sql="SELECT sum(fee) as sum_fee FROM `hnww_fee` where state=0 and deleted=0 and year="+year;
        JSONArray rows_sum_fee0=JSONArray.parseArray(JSONArray.toJSONString(jdbcTemplate.queryForList(sql)));
        if(rows_sum_fee0!=null&&rows_sum_fee0.size()>0)
        {
            sum_fee0=new BigDecimal(jsonUtil.get_double_by_path(rows_sum_fee0.getJSONObject(0).toJSONString(),"$.sum_fee"));
            if(sum_fee0.doubleValue()<0)
            {
                sum_fee0=new BigDecimal(0);
            }
        }
        sql="SELECT sum(fee) as sum_fee FROM `hnww_fee` where state=1 and deleted=0 and year="+year;
        JSONArray rows_sum_fee1=JSONArray.parseArray(JSONArray.toJSONString(jdbcTemplate.queryForList(sql)));
        if(rows_sum_fee1!=null&&rows_sum_fee1.size()>0)
        {
            sum_fee1=new BigDecimal(jsonUtil.get_double_by_path(rows_sum_fee1.getJSONObject(0).toJSONString(),"$.sum_fee"));
            if(sum_fee1.doubleValue()<0)
            {
                sum_fee1=new BigDecimal(0);
            }
        }
        sql="SELECT sum(flow-plan) as sum_above_flow FROM `hnww_fee` where flow>plan and deleted=0 and year="+year;
        JSONArray rows_sum_above_flow=JSONArray.parseArray(JSONArray.toJSONString(jdbcTemplate.queryForList(sql)));
        if(rows_sum_above_flow!=null&&rows_sum_above_flow.size()>0)
        {
            sum_above_flow=new BigDecimal(jsonUtil.get_double_by_path(rows_sum_above_flow.getJSONObject(0).toJSONString(),"$.sum_above_flow"));
            if(sum_above_flow.doubleValue()<0)
            {
                sum_above_flow=new BigDecimal(0);
            }
        }
        sql="SELECT sum(fee-plan*price) as sum_above_fee FROM `hnww_fee` where flow>plan and deleted=0 and year="+year;
        JSONArray rows_sum_above_fee=JSONArray.parseArray(JSONArray.toJSONString(jdbcTemplate.queryForList(sql)));
        if(rows_sum_above_fee!=null&&rows_sum_above_fee.size()>0)
        {
            sum_above_fee=new BigDecimal(jsonUtil.get_double_by_path(rows_sum_above_fee.getJSONObject(0).toJSONString(),"$.sum_above_fee"));
            if(sum_above_fee.doubleValue()<0)
            {
                sum_above_fee=new BigDecimal(0);
            }
        }
        for(int i=1;i<=12;i++)
        {
            JSONObject row_above_info=new JSONObject();
            sql="select year,month,sum(flow-plan) as sum_above_flow,sum(fee-plan*price) as sum_above_fee from hnww_fee where deleted=0 and flow>plan and year="+year+" and month="+i+" GROUP BY year,month";
            row_above_info.put("year",year);
            row_above_info.put("month",i);
            BigDecimal item_sum_above_flow=new BigDecimal(0);
            BigDecimal item_sum_above_fee=new BigDecimal(0);
            JSONArray list_above_info=JSONArray.parseArray(JSONArray.toJSONString(jdbcTemplate.queryForList(sql)));
            if(list_above_info!=null&&list_above_info.size()>0)
            {
                if(list_above_info.getJSONObject(0).get("year")!=null)
                {
                    item_sum_above_flow=new BigDecimal(jsonUtil.get_double_by_path(list_above_info.getJSONObject(0).toJSONString(),"$.sum_above_flow"));
                    item_sum_above_fee=new BigDecimal(jsonUtil.get_double_by_path(list_above_info.getJSONObject(0).toJSONString(),"$.sum_above_fee"));
                }
            }
            row_above_info.put("sum_above_flow",item_sum_above_flow.setScale(2,RoundingMode.HALF_UP).doubleValue());
            row_above_info.put("sum_above_fee",item_sum_above_fee.setScale(2,RoundingMode.HALF_UP).doubleValue());
            sql="select * from hnww_fee where deleted=0 and flow>plan and year="+year+" and month="+i;
            List<Map<String,Object>> list_fee=jdbcTemplate.queryForList(sql);
            if(list_fee.size()>0)
            {
                list_fee=dbService.get_fee_detail(list_fee);
            }
            row_above_info.put("rows_fee",list_fee);
            rows_above_info.add(row_above_info);
        }
        result.put("code",1);
        result.put("message","success");
        result.put("sum_fee",sum_fee);
        result.put("sum_fee0",sum_fee0);
        result.put("sum_fee1",sum_fee1);
        result.put("sum_above_flow",sum_above_flow);
        result.put("sum_above_fee",sum_above_fee);
        result.put("rows_above_info",rows_above_info);
        return result;
    }

    @RequestMapping("/service/get_resource_info")
    private Map<String,Object> get_resource_info(HttpServletRequest request,@RequestParam("sdate") String str_sdate,@RequestParam("edate") String str_edate)
    {
        //当年总费用，已交费用，未缴费用，超额用水总量，每月超额用水总量，某月超额明细
        Map<String,Object> result=new HashMap<>();
        JSONArray rows_resource_info=new JSONArray();
        Date sdate=common.parse_date(str_sdate+" 00:00:00");
        Date edate=common.parse_date(str_edate+" 23:59:59");
        if(sdate==null||edate==null)
        {
            result.put("code",0);
            result.put("msg","参数异常");
            return result;
        }
        List<Date> list_date=new ArrayList<>();
        while(sdate.getTime()<edate.getTime())
        {
            list_date.add(sdate);
            sdate=common.parse_date(common.add_day(sdate,1));
        }
        //获取所有的resource
        String sql="select * from hnww_resource where deleted=0";
        List<Map<String,Object>> list_resource=jdbcTemplate.queryForList(sql);
        if(list_resource.size()>0)
        {
            list_resource=dbService.get_resource_detail(list_resource);
        }
        JSONArray rows_resource=JSONArray.parseArray(JSONArray.toJSONString(list_resource));
        if(rows_resource==null)
        {
            result.put("code",0);
            result.put("msg","水资源站点信息转换json数组失败");
            return result;
        }
        //获取相关的dayflow
        sql="select * from hnww_dayflow where ddate>=? and ddate<=? and device_id in (select id from hnww_device where dtype=1 and deleted=0 and detail_id>0)";
        Object[] ps1=new Object[]{str_sdate+" 00:00:00",str_edate+" 23:59:59"};
        List<Map<String,Object>> list_dayflow=jdbcTemplate.queryForList(sql,ps1);
        JSONArray rows_dayflow=JSONArray.parseArray(JSONArray.toJSONString(list_dayflow));
        if(rows_dayflow==null)
        {
            result.put("code",0);
            result.put("msg","每日流量数据转换json数组失败");
            return result;
        }
        for(int i=0;i<rows_resource.size();i++)
        {
            JSONObject row_resource=rows_resource.getJSONObject(i);
            for(int j=0;j<list_date.size();j++)
            {
                Date item_date=list_date.get(j);
                JSONObject row_resource_info=JSONObject.parseObject(row_resource.toJSONString());
                BigDecimal flow=new BigDecimal(0);
                String flow_date= common.format_date(item_date).substring(0,10);
                JSONArray rows_device=row_resource_info.getJSONArray("rows_device");
                if(rows_device!=null)
                {
                    for(int k=0;k<rows_device.size();k++)
                    {
                        rows_device.getJSONObject(k).put("flow_date",flow_date);
                        int device_id=jsonUtil.get_int_by_path(rows_device.getJSONObject(k).toJSONString(),"$.id");
                        BigDecimal item_flow=new BigDecimal(0);
                        for(int l=0;l<rows_dayflow.size();l++)
                        {
                            String fdate=jsonUtil.get_string_by_path(rows_dayflow.getJSONObject(l).toJSONString(),"$.fdate");
                            int item_device_id=jsonUtil.get_int_by_path(rows_dayflow.getJSONObject(l).toJSONString(),"$.device_id");
                            if(item_device_id==device_id&&fdate.startsWith(flow_date))
                            {
                                item_flow=new BigDecimal(jsonUtil.get_double_by_path(rows_dayflow.getJSONObject(l).toJSONString(),"$.flow")-jsonUtil.get_double_by_path(rows_dayflow.getJSONObject(l).toJSONString(),"$.flow2"));
                                flow=flow.add(item_flow);
                                l=rows_dayflow.size();
                            }
                        }
                        rows_device.getJSONObject(k).put("flow",flow.setScale(2,RoundingMode.HALF_UP).doubleValue());
                    }
                }
                row_resource_info.put("flow_date",flow_date);
                row_resource_info.put("flow",flow.setScale(2,RoundingMode.HALF_UP).doubleValue());
                rows_resource_info.add(row_resource_info);
            }
        }
        result.put("code",1);
        result.put("message","success");
        result.put("data",rows_resource_info);
        return result;
    }

    @RequestMapping("/service/export_resource_info")
    private Map<String,Object> export_resource_info(HttpServletRequest request,@RequestParam("sdate") String str_sdate,@RequestParam("edate") String str_edate)
    {
        //当年总费用，已交费用，未缴费用，超额用水总量，每月超额用水总量，某月超额明细
        Map<String,Object> result=new HashMap<>();
        JSONArray rows_resource_info=new JSONArray();
        Date sdate=common.parse_date(str_sdate+" 00:00:00");
        Date edate=common.parse_date(str_edate+" 23:59:59");
        if(sdate==null||edate==null)
        {
            result.put("code",0);
            result.put("msg","参数异常");
            return result;
        }
        List<Date> list_date=new ArrayList<>();
        while(sdate.getTime()<edate.getTime())
        {
            list_date.add(sdate);
            sdate=common.parse_date(common.add_day(sdate,1));
        }
        //获取所有的resource
        String sql="select * from hnww_resource where deleted=0";
        List<Map<String,Object>> list_resource=jdbcTemplate.queryForList(sql);
        if(list_resource.size()>0)
        {
            list_resource=dbService.get_resource_detail(list_resource);
        }
        JSONArray rows_resource=JSONArray.parseArray(JSONArray.toJSONString(list_resource));
        if(rows_resource==null)
        {
            result.put("code",0);
            result.put("msg","水资源站点信息转换json数组失败");
            return result;
        }
        //获取相关的dayflow
        sql="select * from hnww_dayflow where ddate>=? and ddate<=? and device_id in (select id from hnww_device where dtype=1 and deleted=0 and detail_id>0)";
        Object[] ps1=new Object[]{str_sdate+" 00:00:00",str_edate+" 23:59:59"};
        List<Map<String,Object>> list_dayflow=jdbcTemplate.queryForList(sql,ps1);
        JSONArray rows_dayflow=JSONArray.parseArray(JSONArray.toJSONString(list_dayflow));
        if(rows_dayflow==null)
        {
            result.put("code",0);
            result.put("msg","每日流量数据转换json数组失败");
            return result;
        }
        for(int i=0;i<rows_resource.size();i++)
        {
            JSONObject row_resource=rows_resource.getJSONObject(i);
            for(int j=0;j<list_date.size();j++)
            {
                Date item_date=list_date.get(j);
                JSONObject row_resource_info=JSONObject.parseObject(row_resource.toJSONString());
                BigDecimal flow=new BigDecimal(0);
                String flow_date= common.format_date(item_date).substring(0,10);
                JSONArray rows_device=row_resource_info.getJSONArray("rows_device");
                if(rows_device!=null)
                {
                    for(int k=0;k<rows_device.size();k++)
                    {
                        rows_device.getJSONObject(k).put("flow_date",flow_date);
                        int device_id=jsonUtil.get_int_by_path(rows_device.getJSONObject(k).toJSONString(),"$.id");
                        BigDecimal item_flow=new BigDecimal(0);
                        for(int l=0;l<rows_dayflow.size();l++)
                        {
                            String fdate=jsonUtil.get_string_by_path(rows_dayflow.getJSONObject(l).toJSONString(),"$.fdate");
                            int item_device_id=jsonUtil.get_int_by_path(rows_dayflow.getJSONObject(l).toJSONString(),"$.device_id");
                            if(item_device_id==device_id&&fdate.startsWith(flow_date))
                            {
                                item_flow=new BigDecimal(jsonUtil.get_double_by_path(rows_dayflow.getJSONObject(l).toJSONString(),"$.flow")-jsonUtil.get_double_by_path(rows_dayflow.getJSONObject(l).toJSONString(),"$.flow2"));
                                flow=flow.add(item_flow);
                                l=rows_dayflow.size();
                            }
                        }
                        rows_device.getJSONObject(k).put("flow",flow.setScale(2,RoundingMode.HALF_UP).doubleValue());
                    }
                }
                row_resource_info.put("flow_date",flow_date);
                row_resource_info.put("flow",flow.setScale(2,RoundingMode.HALF_UP).doubleValue());
                rows_resource_info.add(row_resource_info);
            }
        }
        ObjectMapper mapper=new ObjectMapper();
        String uuid=(UUID.randomUUID().toString().replace("-",""));
        String filename=System.getProperty("user.dir")+"/hnweb/static/public/excel/"+uuid+".xlsx";
        String path="/public/excel/"+uuid+".xlsx";
        ExcelWriter builder=excelUtil.get_builder(filename,"resource_info");
        if(builder==null)
        {
            result.put("code",0);
            result.put("msg","创建写入器失败");
            return result;
        }
        try {
            WriteSheet sheet= EasyExcel.writerSheet("resource_info").build();
            List<List<String>> data=excelUtil.get_resource_info_data(rows_resource_info);
            builder.write(data,sheet);
            builder.finish();
            result.put("code",1);
            result.put("msg","success");
            result.put("data",path);
        } catch (Exception e) {
            e.printStackTrace();
            result.put("code",0);
            result.put("msg",common.format_exception(e));
        }
        finally {
            if(builder!=null)
            {
                builder.finish();
            }
        }
        return result;
    }
    //
    @RequestMapping("/service/get_type_chart")
    private Map<String,Object> get_type_chart(HttpServletRequest request,@RequestParam("dtype") int dtype)
    {
        Map<String,Object> res=new HashMap<>();
        int field_id=28;
        String sql="select * from hnww_field where id="+field_id;
        List<Map<String,Object>> list_field=jdbcTemplate.queryForList(sql);
        if(list_field.size()==0)
        {
            String msg="查询监测站类型失败";
            res.put("code",0);
            res.put("msg",msg);
            return res;
        }
        list_field=dbService.get_field_detail(list_field);
        JSONObject row_field=JSONObject.parseObject(JSONObject.toJSONString(list_field.get(0)));
        if(row_field==null)
        {
            String msg="监测站类型字段转换json对象失败";
            res.put("code",0);
            res.put("msg",msg);
            return res;
        }
        String dictionary=jsonUtil.get_string_by_path(row_field.toJSONString(),"$.dictionary");
        if(common.is_empty(dictionary))
        {
            String msg="字段中字典为空";
            res.put("code",0);
            res.put("msg",msg);
            return res;
        }
        String[] list_dictionary=dictionary.split(",");
        JSONArray rows=new JSONArray();
        for(int i=0;i<list_dictionary.length;i++)
        {
            JSONObject row=new JSONObject();
            String item_dictionary=list_dictionary[i];
            String table=dtype==0?"hnww_site":"hnww_resource";
            sql="select count(id) as fcount from hnww_device where field_"+field_id+"=? and deleted=0 and dtype="+dtype+" and detail_id in (select id from "+table+" where deleted=0)";
            List<Map<String,Object>> list_row=jdbcTemplate.queryForList(sql,new Object[]{item_dictionary});
            int fcount=0;
            if(list_row.size()>0)
            {
                if(common.is_int(list_row.get(0).get("fcount")+""))
                {
                    fcount=Integer.parseInt(list_row.get(0).get("fcount")+"");
                }
            }
            row.put("type",item_dictionary);
            row.put("fcount",fcount);
            rows.add(row);
        }
        res.put("code",1);
        res.put("msg","success");
        res.put("data",rows);
        return res;
    }
    @RequestMapping("/service/get_division_chart")
    private Map<String,Object> get_division_chart(HttpServletRequest request,@RequestParam("dtype") int dtype)
    {
        Map<String,Object> res=new HashMap<>();
        String sql="select * from hnww_division where deleted=0";
        List<Map<String,Object>> list_division=jdbcTemplate.queryForList(sql);
        if(list_division.size()==0)
        {
            String msg="查询行政区域失败";
            res.put("code",0);
            res.put("msg",msg);
            return res;
        }
        String table=dtype==0?"hnww_site":"hnww_resource";
        sql="select t1.division_id,count(t2.id) as fcount from "+table+" as t1 left join hnww_device as t2 on t1.id=t2.detail_id where t2.dtype="+dtype+" and t2.deleted=0 and t1.deleted=0 group by t1.division_id";
        List<Map<String,Object>> list_row=jdbcTemplate.queryForList(sql);
        list_division=dbService.fill_row(list_division,list_row,"id","division_id","row_division");
        for(int i=0;i<list_division.size();i++)
        {
            Map<String,Object> row_fcount=(Map<String, Object>) list_division.get(i).get("row_division");
            if(row_fcount==null)
            {
                list_division.get(i).put("fcount",0);
            }
            else {
                if(common.is_int(row_fcount.get("fcount")+""))
                {
                    list_division.get(i).put("fcount",Integer.parseInt(row_fcount.get("fcount")+""));
                }
                else {
                    list_division.get(i).put("fcount",0);
                }
            }
        }
        res.put("code",1);
        res.put("msg","success");
        res.put("data",list_division);
        return res;
    }
    @RequestMapping("/service/get_monthflow")
    private Map<String,Object> get_monthflow(HttpServletRequest request,@RequestParam("dtype") int dtype,@RequestParam("year") int year)
    {
        Map<String,Object> res=new HashMap<>();
        String sql="select * from hnww_division where deleted=0";
        List<Map<String,Object>> list_division=jdbcTemplate.queryForList(sql);
        if(list_division.size()==0)
        {
            String msg="查询行政区域失败";
            res.put("code",0);
            res.put("msg",msg);
            return res;
        }
        String table=dtype==0?"hnww_site":"hnww_resource";
        List<Map<String,Object>> rows_division=new ArrayList<>();
        for(int i=-1;i<list_division.size();i++)
        {
            Map<String,Object> row_division;
            if(i<0)
            {
                row_division=new HashMap<>();
                row_division.put("id",0);
                row_division.put("name","海口市");
            }
            else {
                row_division=list_division.get(i);
            }
            int division_id=Integer.parseInt(row_division.get("id")+"");
            sql="select dyear,dmonth,sum(flow-flow2) as sum_flow from hnww_monthflow where device_id in (select id from hnww_device where dyear="+year+" and dtype="+dtype+" and deleted=0 and detail_id in (select id from "+table+" where deleted=0 "+(division_id>0?(" and division_id="+division_id):"")+")) group by dyear,dmonth";
            List<Map<String,Object>> rows_flow=jdbcTemplate.queryForList(sql);
            List<Map<String,Object>> rows=new ArrayList<>();
            for(int j=1;j<=12;j++)
            {
                Map<String,Object> row=new HashMap<>();
                row.put("dyear",year);
                row.put("dmonth",j);
                int index=-1;
                for(int k=0;k<rows_flow.size();k++)
                {
                    if(common.is_int(rows_flow.get(k).get("dmonth")+"")&&Integer.parseInt(rows_flow.get(k).get("dmonth")+"")==j)
                    {
                        index=k;
                        k=rows_flow.size();
                    }
                }
                if(index==-1)
                {
                    row.put("sum_flow",0);
                }
                else {
                    row.put("sum_flow",Double.parseDouble(rows_flow.get(index).get("sum_flow")+""));
                }
                rows.add(row);
            }
            row_division.put("rows",rows);
            rows_division.add(row_division);
        }
        res.put("code",1);
        res.put("msg","success");
        res.put("data",rows_division);
        return res;
    }
    @RequestMapping("/service/import_site")
    private Map<String,Object> fupdate(HttpServletRequest request,  @RequestParam("row") String row)
    {
        Map<String,Object> res=new HashMap<>();
        res.put("code",1);
        res.put("msg","新增成功");
        ObjectMapper mapper=new ObjectMapper();
        try {
            // 将JSON字符串转换为Map对象
            Map<String, Object> map = mapper.readValue(row, new TypeReference<Map<String, Object>>() {});
            List<Map<String,Object>> columns=dbService.columns("site");
            String sql="select * from hnww_division where deleted=0";
            List<Map<String,Object>> rows_division=jdbcTemplate.queryForList(sql);
            //判断name
            String name=map.get("name")==null?"":map.get("name")+"";
            String field_130=map.get("field_130")==null?"":map.get("field_130")+"";//许可证编号
            String device_sns=map.get("device_sns")==null?"":map.get("device_sns")+"";
            String division_name=map.get("division_name")==null?"":map.get("division_name")+"";
            if(common.is_empty(name))
            {
                throw new Exception("站点名称不可为空");
            }
            //对device_sns进行判断
//            if(!common.is_empty(device_sns))
//            {
//                String[] list_device=device_sns.split(",");
//                sql="select * from hnww_device where sn in ("+common.join_String(list_device)+")";
//                List<Map<String,Object>> rows_device=jdbcTemplate.queryForList(sql);
//                for(int i=0;i<rows_device.size();i++)
//                {
//                    int detail_id=Integer.parseInt(rows_device.get(i).get("detail_id")+"");
//                    if(detail_id>0)
//                    {
//                        throw new Exception(rows_device.get(i).get("sn")+"设备已绑定");
//                    }
//                }
//            }
            //对division_name进行转换
            int division_id=-1;
            for(int i=0;i<rows_division.size();i++)
            {
                if(division_name.equals(rows_division.get(i).get("name")+""))
                {
                    division_id=Integer.parseInt(rows_division.get(i).get("id")+"");
                }
            }
            if(division_id==-1)
            {
                throw new Exception("行政区域不存在");
            }
            map.put("division_id",division_id);
            //is_key
            if(map.get("is_key")==null)
            {
                map.put("is_key",0);
            }
            else if(map.get("is_key").toString().equals("是"))
            {
                map.put("is_key",1);
            }
            else {
                map.put("is_key",0);
            }
            //判断name是否存在
            int id=-1;
            if(!common.is_empty(field_130))
            {
                sql="select * from hnww_site where field_130=? and deleted=0";
                List<Map<String,Object>> rows_site=jdbcTemplate.queryForList(sql,new Object[]{name});
                if(rows_site.size()>0)
                {
                    id=Integer.parseInt(rows_site.get(0).get("id")+"");
                    map.put("id",id+"");
                }
            }
            int res_id=dbService.save(columns,"site",map);
            if(id==-1)
            {
                if(res_id<=0)
                {
                    throw new Exception("插入site失败");
                }
                else
                {
                    id=res_id;
                }
            }
            else {
                res.put("msg","更新成功");
            }
            if(!common.is_empty(device_sns)) {
                String[] list_device = device_sns.split(",");
                sql="update hnww_device set dtype=0,detail_id=? where sn in ("+common.join_String(list_device)+")";
                jdbcTemplate.update(sql,new Object[]{id});
            }
        } catch (Exception e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }
    @RequestMapping("/service/import_permit")
    private Map<String,Object> import_permit(HttpServletRequest request,  @RequestParam("row") String row)
    {
        Map<String,Object> res=new HashMap<>();
        res.put("code",1);
        res.put("msg","success");
        ObjectMapper mapper=new ObjectMapper();
        try {
            // 将JSON字符串转换为Map对象
            Map<String, Object> map = mapper.readValue(row, new TypeReference<Map<String, Object>>() {});
            List<Map<String,Object>> columns=dbService.columns("permit");
            String sql="select * from hnww_division where deleted=0";
            List<Map<String,Object>> rows_division=jdbcTemplate.queryForList(sql);
            //判断name
            String name=map.get("name")==null?"":map.get("name")+"";
            String device_sns=map.get("device_sns")==null?"":map.get("device_sns")+"";
            String division_name=map.get("division_name")==null?"":map.get("division_name")+"";
            if(common.is_empty(name))
            {
                throw new Exception("站点名称不可为空");
            }
            //判断name是否存在
            int id=-1;
            sql="select * from hnww_site where name=? and deleted=0";
            List<Map<String,Object>> rows_site=jdbcTemplate.queryForList(sql,new Object[]{name});
            if(rows_site.size()>0)
            {
                id=Integer.parseInt(rows_site.get(0).get("id")+"");
                map.put("id",id+"");
            }
            int res_id=dbService.save(columns,"site",map);
            if(id==-1)
            {
                if(res_id<=0)
                {
                    throw new Exception("插入site失败");
                }
                else
                {
                    id=res_id;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            res.put("code",0);
            res.put("msg",common.format_exception(e));
        }
        return res;
    }
    @RequestMapping("/service/list_file")
    private Map<String, Object> list_file(@RequestParam("path") String path)
    {
        Map<String,Object> res=new HashMap<>();
        List<Map<String,Object>> data=fileUtil.list_file(new File(path));
        res.put("code",1);
        res.put("msg","success");
        res.put("data",data);
        return res;
    }
    @RequestMapping("/service/download_file")
    private ResponseEntity<InputStreamResource> download_file(@RequestParam("path") String path) throws IOException
    {
        File file=new File(path);
        InputStreamResource resource=new InputStreamResource(new FileInputStream(file));
        HttpHeaders headers=new HttpHeaders();
        headers.add(HttpHeaders.CONTENT_DISPOSITION,"attchment; filename="+file.getName());
        headers.add(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_OCTET_STREAM_VALUE);
        return ResponseEntity.ok().headers(headers).contentLength(file.length()).contentType(MediaType.APPLICATION_OCTET_STREAM).body(resource);
    }
    @RequestMapping("/service/do_sync")
    private Map<String,Object> do_sync(HttpServletRequest request,@RequestParam("device_id") int device_id)
    {
        Map<String,Object> res=new HashMap<>();
        try {
            testJob.do_update_dloglast(device_id);
            testJob.do_update_flow(device_id);
            res.put("code",1);
            res.put("msg","success");
            return res;
        }
        catch (Exception ex)
        {
            res.put("code",0);
            res.put("msg",common.format_exception(ex));
            return res;
        }
    }
    @RequestMapping("/service/to_check_repeat")
    private Map<String,Object> to_check_repeat(HttpServletRequest request,@RequestParam("device_id") int device_id)
    {
        Map<String,Object> res=new HashMap<>();
        try {
            testJob.do_check_repeat(device_id);
            res.put("code",1);
            res.put("msg","success");
            return res;
        }
        catch (Exception ex)
        {
            res.put("code",0);
            res.put("msg",common.format_exception(ex));
            return res;
        }
    }
    @RequestMapping("/service/create_file")
    private Map<String,Object> create_file(@RequestParam("path") String path) throws IOException
    {
        Map<String,Object> res=new HashMap<>();
        try {
            boolean result=fileUtil.create_file(path);
            if(result)
            {
                res.put("code",1);
                res.put("msg","success");
                return res;
            }
            res.put("code",0);
            res.put("msg","保存失败");
            return res;
        }
        catch (Exception ex)
        {
            res.put("code",0);
            res.put("msg",common.format_exception(ex));
            return res;
        }
    }
    @RequestMapping("/service/read_file")
    private Map<String,Object> read_file(@RequestParam("path") String path) throws IOException
    {
        Map<String,Object> res=new HashMap<>();
        try {
            String data=fileUtil.read(path);
            res.put("code",1);
            res.put("msg","success");
            res.put("data",data);
            return res;
        }
        catch (Exception ex)
        {
            res.put("code",0);
            res.put("msg",common.format_exception(ex));
            return res;
        }
    }
    @RequestMapping("/service/save_file")
    private Map<String,Object> save_file(@RequestParam("path") String path,@RequestParam("data") String data) throws IOException
    {
        Map<String,Object> res=new HashMap<>();
        try {
            boolean result=fileUtil.write(path,data);
            if(result)
            {
                res.put("code",1);
                res.put("msg","success");
                return res;
            }
            res.put("code",0);
            res.put("msg","保存失败");
            return res;
        }
        catch (Exception ex)
        {
            res.put("code",0);
            res.put("msg",common.format_exception(ex));
            return res;
        }
    }
    @RequestMapping("/service/upload_levellog")
    private Map<String,Object> upload_levellog(@RequestParam("sctd") String sn,@RequestParam("tm") String fdate,@RequestParam("z") String flevel) throws IOException
    {
        Map<String,Object> res=new HashMap<>();
        String sql="";
        try {
            List<Map<String,Object>> column_levellog=dbService.columns("levellog");
            sql="select * from "+dbService.prefix+"_levellog where sn=? and fdate=? and deleted=0";
            List<Map<String,Object>> list_levellog=jdbcTemplate.queryForList(sql,new Object[]{sn,fdate});
            if(list_levellog.size()==0)
            {
                Map<String,Object> row=new HashMap<>();
                row.put("sn",sn);
                row.put("fdate",fdate);
                row.put("flevel",flevel);
                row.put("deleted",0);
                int id=dbService.save(column_levellog,"levellog",row);
                if(id<=0)
                {
                    res.put("code",0);
                    res.put("msg","插入levellog失败");
                    return res;
                }
            }
            res.put("code",0);
            res.put("msg","保存失败");
            return res;
        }
        catch (Exception ex)
        {
            res.put("code",0);
            res.put("msg",common.format_exception(ex));
            return res;
        }
    }
    @RequestMapping("/service/upload_rainlog")
    private Map<String,Object> upload_rainlog(@RequestParam("sctd") String sn,@RequestParam("tm") String fdate,@RequestParam("drp") String fval,@RequestParam("intv") String finterval) throws IOException
    {
        Map<String,Object> res=new HashMap<>();
        String sql="";
        try {
            List<Map<String,Object>> column_rainlog=dbService.columns("rainlog");
            sql="select * from "+dbService.prefix+"_rainlog where sn=? and fdate=? and deleted=0";
            List<Map<String,Object>> list_levellog=jdbcTemplate.queryForList(sql,new Object[]{sn,fdate});
            if(list_levellog.size()==0)
            {
                Map<String,Object> row=new HashMap<>();
                row.put("sn",sn);
                row.put("fdate",fdate);
                row.put("fval",fval);
                row.put("finterval",finterval);
                row.put("deleted",0);
                int id=dbService.save(column_rainlog,"rainlog",row);
                if(id<=0)
                {
                    res.put("code",0);
                    res.put("msg","插入levellog失败");
                    return res;
                }
            }
            res.put("code",0);
            res.put("msg","保存失败");
            return res;
        }
        catch (Exception ex)
        {
            res.put("code",0);
            res.put("msg",common.format_exception(ex));
            return res;
        }
    }

}
